Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(10).テンポラリ表の非効率的な利用

当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第10弾、最終回です。課題#10の『テンポラリ表の非効率的な利用』について内容を見て行きたいと思います。

• AWS Solutions Architect ブログ: Amazon Redshiftのパフォーマンスチューニングテクニック Top 10

当トピックで言及されている内容は『一時テーブル、便利だけど落とし穴もあるので気を付けてね』というものです。CREATE TEMP TABLE AS SELECTもしくはSELECT INTO構文を用いてテーブルを設ける事で、そのセッションだけで有効となる一時テーブルを作成する事が出来ますが、この手段を用いて作られたテーブルは以下の様なデメリットを生み出します。

• CREATE TABLE - Amazon Redshift
• SELECT INTO - Amazon Redshift

SELECT INTOを使っている場合はCREATE TEMPORARY TABLEに変更すべし、というガイドがありますが、ここでは実際に『一時的に作られた』テーブルの定義がどのようなものになっているかを確認してみたいと思います。

通常のCREATE TABLE文の場合

通常、CREATE TABLE文実行時に列圧縮タイプ、ソートキー、分散キーを指定しておくと、

# CREATE TABLE IF NOT EXISTS public.orders (
  order_id INTEGER NOT NULL  
  ,order_date DATE NOT NULL  
  ,priority VARCHAR(12) NOT NULL  
  ,quantity SMALLINT NOT NULL  
  ,sales DOUBLE PRECISION   
  ,discount_rate DOUBLE PRECISION   
  ,ship_mode VARCHAR(20) NOT NULL  ENCODE bytedict
  ,profit INTEGER NOT NULL  
  ,unit_price INTEGER NOT NULL  
  ,ad_expenses INTEGER NOT NULL  
  ,shipping_cost INTEGER NOT NULL  
  ,customer_name VARCHAR(50) NOT NULL  ENCODE lzo
  ,prefecture VARCHAR(12) NOT NULL  
  ,city VARCHAR(20) NOT NULL  
  ,area VARCHAR(12) NOT NULL  
  ,shop_name VARCHAR(20) NOT NULL  
  ,customer_segment VARCHAR(30) NOT NULL  ENCODE bytedict
  ,product_category VARCHAR(30) NOT NULL  ENCODE bytedict
  ,product_sub_category VARCHAR(100) NOT NULL  ENCODE lzo
  ,product_id VARCHAR(10) NOT NULL  
  ,product_name VARCHAR(100) NOT NULL  ENCODE lzo
  ,product_description VARCHAR(200) NOT NULL  ENCODE lzo
  ,product_container VARCHAR(100) NOT NULL  ENCODE lzo
  ,base_margin DOUBLE PRECISION   
  ,supplier VARCHAR(30) NOT NULL  
  ,deliver_date DATE NOT NULL  
  ,ship_date DATE NOT NULL  
  ,PRIMARY KEY (order_id)
)
DISTKEY(order_id)
SORTKEY(order_date);

このような形で定義は指定の内容となります。これはまぁ普通ですね。

# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'orders';
 schemaname | tablename |        column        |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+----------------------+------------------------+----------+---------+---------+---------
 public     | orders    | order_id             | integer                | none     | t       |       0 | t
 public     | orders    | order_date           | date                   | none     | f       |       1 | t
 public     | orders    | priority             | character varying(12)  | none     | f       |       0 | t
 public     | orders    | quantity             | smallint               | none     | f       |       0 | t
 public     | orders    | sales                | double precision       | none     | f       |       0 | f
 public     | orders    | discount_rate        | double precision       | none     | f       |       0 | f
 public     | orders    | ship_mode            | character varying(20)  | bytedict | f       |       0 | t
 public     | orders    | profit               | integer                | none     | f       |       0 | t
 public     | orders    | unit_price           | integer                | none     | f       |       0 | t
 public     | orders    | ad_expenses          | integer                | none     | f       |       0 | t
 public     | orders    | shipping_cost        | integer                | none     | f       |       0 | t
 public     | orders    | customer_name        | character varying(50)  | lzo      | f       |       0 | t
 public     | orders    | prefecture           | character varying(12)  | none     | f       |       0 | t
 public     | orders    | city                 | character varying(20)  | none     | f       |       0 | t
 public     | orders    | area                 | character varying(12)  | none     | f       |       0 | t
 public     | orders    | shop_name            | character varying(20)  | none     | f       |       0 | t
 public     | orders    | customer_segment     | character varying(30)  | bytedict | f       |       0 | t
 public     | orders    | product_category     | character varying(30)  | bytedict | f       |       0 | t
 public     | orders    | product_sub_category | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | product_id           | character varying(10)  | none     | f       |       0 | t
 public     | orders    | product_name         | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | product_description  | character varying(200) | lzo      | f       |       0 | t
 public     | orders    | product_container    | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | base_margin          | double precision       | none     | f       |       0 | f
 public     | orders    | supplier             | character varying(30)  | none     | f       |       0 | t
 public     | orders    | deliver_date         | date                   | none     | f       |       0 | t
 public     | orders    | ship_date            | date                   | none     | f       |       0 | t
(27 rows)

列圧縮タイプが設定されないケース(1).SELECT INTO文を使った場合

SELECT INTO文を使って一時テーブルを用意するとどうなるのでしょうか。

• SELECT INTO - Amazon Redshift

上記内容を活用しつつ作成してみます。内容はこの様になりました。分散キー、ソートキーの指定はされていますが、列圧縮タイプ(encoding)は全てnoneとなってしまっています。

# INSERT INTO public.orders (SELECT * FROM public.orders_backup ORDER BY order_date);
INSERT 0 8369
# SELECT order_date, order_id, sales INTO public.orders_tmptable FROM public.orders;
SELECT
# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'orders_tmptable';
 schemaname |    tablename    |   column   |       type       | encoding | distkey | sortkey | notnull 
------------+-----------------+------------+------------------+----------+---------+---------+---------
 public     | orders_tmptable | order_date | date             | none     | f       |       1 | f
 public     | orders_tmptable | order_id   | integer          | none     | t       |       0 | f
 public     | orders_tmptable | sales      | double precision | none     | f       |       0 | f
(3 rows)

列圧縮タイプが設定されないケース(2).CREATE TEMP(ORARY) TABLE AS SELECT文を使った場合

2つ目はCREATE TEMP(ORARY) TABLE AS SELECT文です。略してCTASとも呼ばれたりしますね。

• CREATE TABLE AS - Amazon Redshift

こちらの場合も同様に列圧縮タイプが無指定(none)になってしまいました。

# CREATE TEMPORARY TABLE orders_tmptable2nd AS (SELECT order_date, order_id, profit FROM public.orders ORDER BY order_date);
SELECT
# \d orders_tmptable2nd;
      Table "pg_temp_4.orders_tmptable2nd"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 order_date | date                  | 
 order_id   | integer               | 
 profit     | integer               | 

# SELECT * FROM pg_table_def WHERE schemaname = 'pg_temp_4' AND tablename = 'orders_tmptable2nd';
 schemaname |     tablename      |   column   |       type       | encoding | distkey | sortkey | notnull 
------------+--------------------+------------+------------------+----------+---------+---------+---------
 pg_temp_4  | orders_tmptable2nd | order_date | date             | none     | f       |       1 | f
 pg_temp_4  | orders_tmptable2nd | order_id   | integer          | none     | t       |       0 | f
 pg_temp_4  | orders_tmptable2nd | sales      | double precision | none     | f       |       0 | f
(3 rows)

まとめ

という訳であわせて計10個のトピックでお送りしてきました『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』、これにて終了となります(と言いつつも"もうちょっとだけ続くんじゃ(by 亀仙人)")。いずれもパフォーマンスの向上に取って重要なポイントとなるのでRedshiftでDWH環境を作成して行く際には念頭に起きつつ作業を進めて行って頂ければと思います。こちらからは以上です。